package com.vector.utils;

import com.vector.pojo.IsMerged;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;

import javax.swing.filechooser.FileSystemView;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelPoiUtils {


    /**
     * 获得表格里的值判断类型
     *
     * @param thiscell
     * @return
     */
    public String getValue(Cell thiscell) {
        String cellValue = "";
        int cellType = thiscell.getCellType();

        switch (cellType) {
            case XSSFCell.CELL_TYPE_STRING://字符串
//                System.out.print("[STRING]");
                cellValue = thiscell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN://布尔
//                System.out.print("[BOOLEAN]");
                cellValue = String.valueOf(thiscell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK://空
//                System.out.print("[BLANK]");
                break;
            case XSSFCell.CELL_TYPE_NUMERIC://数字（日期、普通数字）
//                System.out.print("[NUMERIC]");
                if (HSSFDateUtil.isCellDateFormatted(thiscell)) {
//                    System.out.print("[日期]");
                    Date date = thiscell.getDateCellValue();
                    cellValue = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
                } else {
//                    System.out.print("[转换成字符串输出]");
                    thiscell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cellValue = thiscell.toString();
                }
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                System.out.print("[数据类型错误]");
                break;
        }
        return cellValue;
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param firstRow 开始行
     * @param lastRow  结束行
     * @param firstCol 开始列
     * @param lastCol  结束列
     */
    private void makeMergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {

        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 判断是否合并单元格
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public IsMerged isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return new IsMerged(true, firstRow + 1, lastRow + 1, firstColumn + 1, lastColumn + 1);
                }
            }
        }
        return new IsMerged(false, 0, 0, 0, 0);
    }

    /**
     * 根据web传来的文件获取里面的主键
     *
     * @param file1
     * @return
     * @throws IOException
     */
    public List<String> readText(FileInputStream file1) throws IOException {
        List<String> list = new ArrayList<>();//先创建一个集合
        String cellValue = "";
        list.add("标题");// 0 1 2 3

        Workbook workbook = new XSSFWorkbook(file1);

        Sheet sheet = workbook.getSheetAt(0);

        int rows = sheet.getLastRowNum();
        System.out.println("A表行共有" + rows);
        for (int row = 1; row <= rows; row++) {

            Row thisrow = sheet.getRow(row);

            if (thisrow == null) {
                System.out.println("A表第" + row + "行为空");
                continue;
            } else {
                Cell thiscell = thisrow.getCell(19);

                if (thiscell == null) {
                    list.add("null");
                    System.out.println("存null,该表格没有值：（" + row + ",19)");
                    continue;
                } else {
                    cellValue = getValue(thiscell);
                    list.add(cellValue);
                }
            }
        }
        file1.close();
        return list;
    }

    /**
     * 根据B表查询内容,把数据导出
     *
     * @param file2
     * @param list
     * @return
     * @throws IOException
     */
    public String queryString(FileInputStream file2, List<String> list) throws IOException {
        String cellValue = "";
        String cellValueNext = "";
        String cellValueNextTwo = "";
        File homeDirectory = FileSystemView.getFileSystemView().getHomeDirectory();//拿到该电脑桌面的路径
        String Path3 = homeDirectory + "";//把该路径转换成字符串

        Workbook workbook = new XSSFWorkbook(file2);// 把传来的excel变成workbook
        Sheet sheet = workbook.getSheetAt(0);//拿到sheet页
        int rows = sheet.getLastRowNum();//拿到总行数

        for (int i = 1; i < list.size(); i++) {//已补充位置，所以list从1开始
            if (list.get(i).equals("null")) {//判断list里面的数据是什么，如果是null，那么就跳过
                System.out.println("list中为null的第" + i + "个");
                continue;
            } else {
                for (int row = 1; row <= rows; row++) {//总共有rows行提供数据
                    Row thisrow = sheet.getRow(row);
                    if (thisrow == null) {
                        System.out.println("B表该行为空:" + row + "（这种事情应该不会发生，可能数据提供残缺）");
                    } else {
                        Cell thiscell = thisrow.getCell(0);
                        if (thiscell == null) {
                            System.out.println("B表表格为没值:" + "(" + row + ",0)");
                        } else {
                            String thisvalue = getValue(thiscell);

                            if (list.get(i).length() >= 12) {//提取12位字符串,
                                if (thisvalue.equals(list.get(i).substring(0, 12))) {
//                System.out.println((row+1) + "__过程__" +thisvalue);
                                    cellValue = thisvalue;
                                    Cell cellEnd = thisrow.getCell(1);
                                    cellValueNext = getValue(cellEnd);
                                    Cell cellEndTwo = thisrow.getCell(2);
                                    if (cellEndTwo == null) {
                                        continue;
                                    }
                                    cellValueNextTwo = getValue(cellEndTwo);

                                    Workbook wb = new XSSFWorkbook();
                                    Sheet st = wb.createSheet();
                                    CellStyle cellStyle = wb.createCellStyle();
                                    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);

                                    Row r1 = st.createRow(0);
                                    Row r2 = st.createRow(1);
                                    Row r3 = st.createRow(2);
                                    Row r4 = st.createRow(3);
                                    Cell a1 = r1.createCell(0);
                                    Cell c1 = r1.createCell(2);
                                    Cell a2 = r2.createCell(0);
                                    Cell a3 = r3.createCell(0);

                                    makeMergeRegion(st,1,1,1,3);
                                    makeMergeRegion(st,2,2,0,3);
                                    makeMergeRegion(st,3,9,0,3);

                                    a1.setCellValue("WBS元素");
                                    c1.setCellValue("项目定义");
                                    a2.setCellValue("单位");
                                    a3.setCellValue("描述");

                                    Cell c12 = r1.createCell(1);
                                    Cell c14 = r1.createCell(3);
                                    Cell c22 = r2.createCell(1);
                                    Cell c41 = r4.createCell(0);
                                    c41.setCellStyle(cellStyle);

                                    c12.setCellValue(list.get(i));
                                    c14.setCellValue(list.get(i).substring(0, 12));
                                    c22.setCellValue(cellValueNext);
                                    c41.setCellValue(cellValueNextTwo);

                                    FileOutputStream fileOut = new FileOutputStream(Path3+"\\第"+i+"个表格.xlsx");
                                    wb.write(fileOut);
                                    fileOut.close();

                                }
                            } else {
                                System.out.println("字符串长度不足12个:" + (i + 1));
                            }
                        }
                    }
                }
            }
        }

        file2.close();
        System.out.println("文件生成完毕");

        return "ok";
    }

    /**
     * 根据B表查询内容,填入第三表
     *
     * @param file2
     * @param list
     * @return
     * @throws IOException
     */
    public String exportExcel(FileInputStream file2,FileInputStream file3, List<String> list) throws IOException {
        String cellValue = "";
        String cellValueNext = "";
        String cellValueNextTwo = "";
        FileOutputStream fileOut = null;
        File homeDirectory = FileSystemView.getFileSystemView().getHomeDirectory();
        String Path3 = homeDirectory+"";


        Workbook workbook = new XSSFWorkbook(file2);
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getLastRowNum();

        for (int i = 1; i < list.size(); i++) {//已补充位置，所以list从1开始
            FileInputStream f1 = file3;
            if (list.get(i).equals("null")) {
                System.out.println("list中为null的第" + i + "个");
                continue;
            } else {
                for (int row = 1; row <= rows; row++) {//总共有rows行提供数据
                    Row thisrow = sheet.getRow(row);
                    if (thisrow == null) {
                        System.out.println("B表该行为空:" + row + "（这种事情应该不会发生，可能数据提供残缺）");
                    } else {
                        Cell thiscell = thisrow.getCell(0);
                        if (thiscell == null) {
                            System.out.println("B表表格为没值:" + "(" + row + ",0)");
                        } else {
                            String thisvalue = getValue(thiscell);

                            if (list.get(i).length() >= 12) {//提取12位字符串,
                                if (thisvalue.equals(list.get(i).substring(0, 12))) {
//                System.out.println((row+1) + "__过程__" +thisvalue);
                                    cellValue = thisvalue;
                                    Cell cellEnd = thisrow.getCell(1);
                                    cellValueNext = getValue(cellEnd);
                                    Cell cellEndTwo = thisrow.getCell(2);
                                    if (cellEndTwo == null) {
                                        continue;
                                    }
                                    cellValueNextTwo = getValue(cellEndTwo);
//                                    list.get(i)                  wbs元素
//                                    list.get(i).substring(0, 12) 项目定义
//                                    cellValueNext)               描述
//                                    cellValueNextTwo)            单位
                                    Workbook wb = new XSSFWorkbook(f1);
                                    Sheet st = wb.getSheetAt(0);
                                    Row r1 = st.getRow(0);
                                    Row r2 = st.getRow(1);
                                    Row r4 = st.getRow(3);

                                    Cell c12 = r1.getCell(1);
                                    Cell c14 = r1.getCell(3);
                                    Cell c22 = r2.getCell(1);
                                    Cell c41 = r4.getCell(0);

                                    c12.setCellValue(list.get(i));
                                    c14.setCellValue(list.get(i).substring(0, 12));
                                    c22.setCellValue(cellValueNext);
                                    c41.setCellValue(cellValueNextTwo);

                                    fileOut = new FileOutputStream(Path3+"\\第"+i+"个表格.xlsx");
                                    wb.write(fileOut);
                                    fileOut.close();

                                }
                            } else {
                                System.out.println("字符串长度不足12个:" + (i + 1));
                            }
                        }
                    }
                }
            }
        }

        file2.close();
        file3.close();
        System.out.println("文件生成完毕");

        return "ok";
    }


}
